
package BOH.GUI;

import BOH.data.TableConnection;
import BOH.data.EateryDBConnection;
import java.awt.*;
import java.awt.event.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import javax.swing.*;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;

/**
 * The Back of House System for a
 * small restaurant.
 * 
 * Maeghan Colvin & Jennifer Havemman
 * CSC 289 0B01
 * February 13, 2014
 */
public class InsertPayrollPanel extends JPanel
{
    // Variables
    private JFrame frame;
    private JPanel mainPanel;               // Holds the mainPanel
    private JPanel optionsPanel;            // Holds the optionsPanel
    private JPanel buttonPanel;             // Holds the buttonPanel
    private JPanel tempPanel;               // Holds tempPanel for formatting
    private JLabel employeeIDLabel;         // Holds the employeeID Label   
    private JLabel nameLabel;               // Holds the nameLabel
    private JLabel startingDateLabel;       // Holds the startingDateLabel
    private JLabel endingDateLabel;         // Holds endingDateLabel
    private JComboBox employeeIDBox;        // Holds the employee ID information
    private JComboBox nameBox;              // Holds the name
    private JSpinner startingDateSpinner;   // Holds startingDateSpinner
    private JSpinner endingDateSpinner;     // Holds endingDateSpinner
    private SpinnerDateModel model;         // Holds the model for startingDate
    private SpinnerDateModel model2;        // Holds the model for endingDate
    private String startingDate;            // Holds the startingDate
    private String endingDate;              // Holds the endingDate 
    private SimpleDateFormat dateFormat;    // Holds the date format
    private String[] employeeIDArray;       // Fills the employeeIDBox
    private String[] nameArray;             // Fills the nameBox
    private String[] accumulatedTimeArray;  // Accumulated hours an employee worked
    private String sqlStatement;            // Selects the employeeID for combobox
    private String sqlStatement2;           // Selects last, first name for combobox
    private String sqlStatement3;           // Selects the employee hours worked
    private String sqlStatement4;           // Selects the employee pay rate
    private String sqlStatement5;           // Inserts pay roll information
    private String sqlStatement6;           // Selects the payroll table
    private String employeeID = "100";      // Holds the employeeID
    private double payRate;                 // Holds the employees pay rate
    private double employeePay;             // Hold the employee regular pay
    private double overtimePayRate = 0;     // Holds the employee overtime pay rate
    private double employeeOvertimeHours = 0;// Holds the overtime hours an employee worked
    private double employeeOvertimePay = 0; // Holds the overtime payment amount
    private double hoursWorked;             // Holds the amount of hours the employee worked
    private double employeeGrossIncome;     // Holds the employees gross income
    private JButton insertButton;           // Views the payroll results
    private String employeeIDSelection;     // Holds the employeeID from comboBox
    private String nameSelection;           // Holds the nameSelection
    private int nameBoxIndex;               // Hold the index to find proper employeeID
    
    /**
     * Constructor
     */
    
    public InsertPayrollPanel()
    {
        // Set Look and Feel.
        JFrame.setDefaultLookAndFeelDecorated(true);
        
        // Create and set up the window.
        frame = new JFrame("Payroll");
        frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        
        // Build and add mainPanel
        this.buildMainPanel();
        frame.add(mainPanel);
        
        // Add content to the window.
        frame.setSize(300, 300);
        frame.validate();
        
        // Display the window.
        frame.setVisible(true);  
    }
    
    /**
     * The buildMainPanel builds the 
     * mainPanel.
     */
    
    private void buildMainPanel() 
    {
        // Create mainPanel
        mainPanel = new JPanel(new BorderLayout());
        mainPanel.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));
        
        // Get optionsPanel
        buildOptionsPanel();
        
        // Get buttonPanel
        buildButtonPanel();
        
        // Add panels
        mainPanel.add(optionsPanel, BorderLayout.CENTER);
        mainPanel.add(buttonPanel, BorderLayout.SOUTH);
    }
    
     /**
     * The buildOptionsPanel builds the 
     * optionsPanel.
     */
    
    private void buildOptionsPanel() 
    {
        // Create optionsPanel
        optionsPanel = new JPanel(new FlowLayout(FlowLayout.LEFT));
        optionsPanel.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));
        
        // Create optionsPanel
        tempPanel = new JPanel(new GridLayout(8, 0));
        tempPanel.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));
        
        // Create the JLabels
        employeeIDLabel = new JLabel("Employee ID: ");
        nameLabel = new JLabel("Employee Name: ");
        startingDateLabel = new JLabel("Starting Date: ");
        endingDateLabel = new JLabel("Ending Date: ");
        
        // Fill the Arrays for the combo boxes
        employeeIDArray = getEmployeeID();
        nameArray = getEmployeeName();
        
        // Create combo boxes
        employeeIDBox = new JComboBox(employeeIDArray);
        employeeIDBox.setToolTipText("Select employee ID");
        employeeIDBox.addActionListener(new EmployeeIDComboBoxListener());
        nameBox = new JComboBox(nameArray);
        nameBox.setToolTipText("Select a name");
        nameBox.addActionListener(new NameComboBoxListener());
        
        // Create dates for spinners
        dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();
        Date initDate = calendar.getTime();
        calendar.add(Calendar.YEAR, -100);
        Date earliestDate = calendar.getTime();
        calendar.add(Calendar.YEAR, 200);
        Date latestDate = calendar.getTime();
                
        // Create startingDateSpinner model
        model = new SpinnerDateModel(initDate, //initial value
                               earliestDate, //min
                               latestDate, //max
                               Calendar.YEAR);                //step
        
        // Create endingDateSpinner model
        model2 = new SpinnerDateModel(initDate, //initial value
                               earliestDate, //min
                               latestDate, //max
                               Calendar.YEAR);                //step
        
        // Create sstartingDateSpinner
        startingDateSpinner = new JSpinner(model);
        startingDateSpinner.setToolTipText("Select a starting date");
        startingDateSpinner.setEditor(new JSpinner.DateEditor(startingDateSpinner, dateFormat.toPattern()));
        startingDateSpinner.addChangeListener(new StartingDateSpinnerListener());
        
        // Create endingDateSpinner
        endingDateSpinner = new JSpinner(model2);
        endingDateSpinner.setToolTipText("Select a an ending date");
        endingDateSpinner.setEditor(new JSpinner.DateEditor(endingDateSpinner, dateFormat.toPattern()));
        endingDateSpinner.addChangeListener(new EndingDateSpinnerListener());
        
        // Add components to panel
        tempPanel.add(employeeIDLabel);
        tempPanel.add(employeeIDBox);
        tempPanel.add(nameLabel);
        tempPanel.add(nameBox);
        tempPanel.add(startingDateLabel);
        tempPanel.add(startingDateSpinner);
        tempPanel.add(endingDateLabel);
        tempPanel.add(endingDateSpinner);
        optionsPanel.add(tempPanel);
    }
    
    /**
     * The buildButtonPanel builds the 
     * buttonPanel.
     */
    
    private void buildButtonPanel() 
    {
        // Create buttonPanel
        buttonPanel = new JPanel(new BorderLayout());
        buttonPanel.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));
        
        // Create button
        insertButton = new JButton("Insert");
        insertButton.setMnemonic(KeyEvent.VK_I);
        insertButton.addActionListener(new InsertButtonActionListener());
        
        // Add components to panel
        buttonPanel.add(insertButton, BorderLayout.CENTER);
    }
    
    /**
     * The getEmployeeIDBoxSelection method returns
     * the selected item in the comboBox
     * @param e 
     * @return 
     */
    
    public String getEmployeeIDBoxSelection()
    {
        // Get the selected option
        employeeIDSelection = (String) employeeIDBox.getSelectedItem();
        return employeeIDSelection;
    }
    
    /**
     * The getNamesBoxSelection method returns
     * the selected item in the comboBox
     * @param e 
     * @return 
     */
    
    public int getNamesBoxSelection()
    {
        // Call the employeeIDArray
        employeeIDArray = getEmployeeID();
        
        // Get the selected option
        nameSelection = (String) nameBox.getSelectedItem();
        
        // Get the selected index
        nameBoxIndex = java.util.Arrays.asList(nameArray).indexOf(nameSelection);
        
        // Return
        return nameBoxIndex;
    }
    
    /**
     * The getEmployeeID method gets the names
     * to fill the employeeID combo box.
     * @return
     */
    
    public String[] getEmployeeID()
    {
        sqlStatement = "SELECT EmployeeID " +
                       "FROM Employee";
        
        employeeIDArray = EateryDBConnection.getData(sqlStatement);
        
        // Return
        return employeeIDArray;
    }
    
    /**
     * The getEmployeeName method gets employee
     * names to fill the Name combo box.
     * @return
     */
    
    public String[] getEmployeeName()
    {
        sqlStatement2 = "SELECT LastName || ', ' || FirstName " +
                        "FROM Employee";
        
        nameArray = EateryDBConnection.getData(sqlStatement2);
        
        // Return
        return nameArray;
    }
    
    /**
     * The getEmployeeTimeWorked method gets the
     * times that the employee worked.
     * @return
     */
    
    public String[] getEmployeeTimeWorked()
    {
        sqlStatement3 = "SELECT TotalTime " +
                        "FROM ClockInOut " + 
                        "WHERE EmployeeID = " + employeeID + " " +
                        "AND TotalTime IS NOT NULL " + 
                        "AND ClockIn >= '" + startingDate + "%' " +
                        "AND ClockOut <= '" + endingDate + "%' ";
        
        accumulatedTimeArray = EateryDBConnection.getData(sqlStatement3);
        
        // Return
        return accumulatedTimeArray;
    }
    
    /**
     * The getEmployeePayRate method gets pay 
     * rate for the proper employee.
     * @return
     */
    
    public double getEmployeePayRate()
    {
        sqlStatement4 = "SELECT HourlyWage " +
                        "FROM Employee " + 
                        "WHERE EmployeeID = " + employeeID;
        
        String pay = EateryDBConnection.getDataString(sqlStatement4);
        
        payRate = Double.parseDouble(pay);
        
        // Return
        return payRate;
    }
    
    /**
     * The getEmployeeOvertimePayRate method gets 
     * overtime pay rate for the proper employee.
     * @return
     */
    
    public double getEmployeeOvertimePayRate()
    {
        // Find overtime amount
        double price = payRate / 2;
        
        // Add amount to overtime
        overtimePayRate = payRate + price;
        
        // Return
        return overtimePayRate;
    }
    
    /**
     * The getEmployeeTotalHoursWorked method gets 
     * the total hoursWorked worked.
     * @return
     */
    
    public String getEmployeeTotalHoursWorked()
    {
        int hour = 0;           // Holds the hours worked
        int minute = 0;         // Holds the minutes worked
        int second = 0;         // Holds the seconds worked

        // Search through the accumulatedTimeArray
        for(int index = 0; index < accumulatedTimeArray.length; index += 1) 
        {
            // Split the array by hours, minutes, and seconds
            String[] time = accumulatedTimeArray[index].split(":");
            
            // Add the hours, minutes, and seconds
            hour += Integer.parseInt(time[0]);
            minute += Integer.parseInt(time[1]);
            second += Integer.parseInt(time[2]);
        }
        
        // Calculate the times
        minute += second / 60;
        hour += minute / 60;
        minute %= 60;
        second %= 60;
        
        String result;
       
        // Combine hours and minutes to a string.
        result = getPaddedVariable(hour) + "." + getPaddedVariable(minute);
        
        // Return
        return result;
    }
    
    /**
     * The getPaddedVariable method creates 
     * a padded variable.
     * @return
     */
    
    public String getPaddedVariable(int paddedVariable)
    {
        String padded = "";
        if (paddedVariable < 10)
        {
            padded = "0" + paddedVariable;
        }
        else
        {
            padded = Integer.toString(paddedVariable);
        }
        return padded;
    }
    
    /**
     * The getEmployeeGrossPay method gets 
     * the gross pay for an employee.
     * @return
     */
    
    public double getEmployeeGrossPay()
    {
        // Get total hours worked
        String totalHoursWorked = getEmployeeTotalHoursWorked();
        hoursWorked = Double.parseDouble(totalHoursWorked);
        
        // Stores employeeGrossPay
        double employeeGrossPay = 0;
        
        // If not overtime then calculate payroll
        if (hoursWorked <= 40)
        {
            employeeGrossPay = payRate * hoursWorked; 
        }
        else if (hoursWorked >= 41)
        {
            // Determine overtime hours
            employeeOvertimeHours = hoursWorked - 40;
            hoursWorked -= employeeOvertimeHours;
            
            employeeOvertimePay = employeeOvertimeHours * overtimePayRate;
            employeePay = hoursWorked * payRate;
            employeeGrossPay = employeePay + employeeOvertimePay;
        }
        
        // Return
        return employeeGrossPay;
    }
    
    /**
     * The insertPayrollInformation method inserts 
     * payroll information for the proper employee.
     * @return
     */
    
    public String insertPayrollInformation()
    {
        // Create current date object for the 
        // last pay date
        Date date = new Date();
        
        sqlStatement5 = "INSERT INTO Payroll (" + 
                        "EmployeeID, HoursWorked, PayRate, " +
                        "HourlyTotal, OvertimeHoursWorked, " +
                        "OvertimePayRate, OvertimeTotal, " +
                        "GrossIncome, NetIncome, PayDate) " +
                        "VALUES ( " + employeeID + ", " +
                        hoursWorked + ", " + payRate + ", " +
                        employeePay + ", " + employeeOvertimeHours + ", " +
                        overtimePayRate + ", " + employeeOvertimePay + ", " +
                        employeeGrossIncome + ", " + employeeGrossIncome + ", '" +
                        dateFormat.format(date) + "') ";
        
        // Return
        return sqlStatement5;
    }
    
     /*
     * The EmployeeIDComboBoxListener is used to collect
     * the employee ID or the first employee when nothing is selected.
     */
    
    private class EmployeeIDComboBoxListener implements ActionListener 
    {
        @Override
        public void actionPerformed(ActionEvent e) 
        {
            employeeIDSelection = getEmployeeIDBoxSelection();
            employeeID = employeeIDSelection;
        }
    }
    
    /*
     * The NameComboBoxListener is used to collect
     * the employee name that is selected and
     * matches it with the proper employeeID number.
     */
    
    private class NameComboBoxListener implements ActionListener 
    {
        @Override
        public void actionPerformed(ActionEvent e) 
        {
            nameBoxIndex = getNamesBoxSelection();
            nameSelection = employeeIDArray[nameBoxIndex];
            employeeID = nameSelection;
        }
    }
    
    /*
     * The startingDateSpinnerListener is used to 
     * retrieve the starting date for pay roll.
     */
    
    private class StartingDateSpinnerListener implements ChangeListener 
    {
        @Override
        public void stateChanged(ChangeEvent e) 
        {

                startingDateSpinner = (JSpinner)(e.getSource());
                startingDate = dateFormat.format(startingDateSpinner.getValue());
        }
    }
    
    /*
     * The EndingDateSpinnerListener is used to 
     * retrieve the ending date for pay roll.
     */
    
    private class EndingDateSpinnerListener implements ChangeListener 
    {
        @Override
        public void stateChanged(ChangeEvent e) 
        {

                endingDateSpinner = (JSpinner)(e.getSource());
                endingDate = dateFormat.format(endingDateSpinner.getValue());
        }
    }
    
    /*
     * The InsertButtonActionListener is used to
     * view the payroll information.
     */
    
    private class InsertButtonActionListener implements ActionListener 
    {
        @Override
        public void actionPerformed(ActionEvent e) 
        {
            sqlStatement6 = "SELECT * " +
                            "FROM Payroll " +
                            "WHERE EmployeeID = " + employeeID;
            
            // Get payroll information
            accumulatedTimeArray = getEmployeeTimeWorked();
            payRate = getEmployeePayRate();
            overtimePayRate = getEmployeeOvertimePayRate();
            employeeGrossIncome = getEmployeeGrossPay();
            sqlStatement5 = insertPayrollInformation();
            EateryDBConnection.updateDatabase(sqlStatement5);
            frame.setVisible(false);
            
            // View the table after insert
            TableConnection dbQuery = new TableConnection(sqlStatement6);
            String[] columnNames = dbQuery.getColumnNames();
            String[][] tableData = dbQuery.getTableData();
            GetTableResults table = new GetTableResults(tableData, columnNames);
        }
    }
}
